package geekzpacho.examples.log.p6spy;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class GazpachoP6spy {

	public static void loadDriver() {
		
		try {
			//Class.forName("com.mysql.jdbc.Driver").newInstance();
			//Changed mysql driver to P6SpyDriver
			Class.forName("com.p6spy.engine.spy.P6SpyDriver").newInstance();
			
		} catch (Exception ex) {
			System.out.println("Error Exception loading Driver:" + ex);
		}
	}

	// ------ SQL ----------
	//CREATE SCHEMA `geekpacho` ;
	//
	//CREATE TABLE `vehicles` (
    //	  `idVehicle` int(11) NOT NULL,
    //	  `name` varchar(45) DEFAULT NULL,
    //	  `description` varchar(150) DEFAULT NULL,
    //	  PRIMARY KEY (`idVehicle`)
    //	) 
	
	public static void main(String... a) {

		GazpachoP6spy.loadDriver();

		// Define SQL Java object variables
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;

		// Configure Connection parameters
		String user = "root";
		String password = "root";
		String url = "jdbc:mysql://localhost:3306/geekzpacho";

		try {
			//Open Connection to DB
			conn = DriverManager.getConnection(url, user, password);
			
			//Insert a record in BD
			String insert = "INSERT INTO Vehicles(idVehicle,name,description) values(?,?,?)";
			
			pstmt1 = conn.prepareStatement(insert);
			pstmt1.setInt(1, 1);
			pstmt1.setString(2,"Car");
			pstmt1.setString(3,"Motor vehicle small or medium size, used for carrying people and can accommodate no more than nine seats.");
			
			pstmt1.executeUpdate();
			System.out.println("information entered...");
			
			//Select the record in BD
			String query = "SELECT idVehicle,name,description FROM Vehicles " +
			"WHERE idVehicle = ?";
			
			pstmt2 = conn.prepareStatement(query);
			pstmt2.setInt(1, 1);
			rs = pstmt2.executeQuery();
			
			while(rs.next()) {
				System.out.println("idVehicle: " + rs.getInt(1));
				System.out.println("name: " + rs.getString(2));
				System.out.println("description: " + rs.getString(3));
				System.out.println();
			}
			
		} catch (Exception ex) {
			System.err.println("Error DriverManager.getConnection(): " + ex);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstmt1 != null) {
					pstmt1.close();
				}
				if (pstmt2 != null) {
					pstmt2.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch(SQLException ex){ 
			}
		}
	}
}